﻿namespace Chain.DAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class SysUserWork
    {
        public int Add(Chain.Model.SysUserWork model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into SysUserWork(");
            builder.Append("UserID,StartTime,EedTime,AddNewUser,CardMoney,ExpenseSumMoneys,ExpenseBinkMoneys,ExpenseCouponMoneys,SRechargeMoney,FRechargeMoney,RechargeBank,FRechargeGiveMoney,AllMoneys,sjMoneys,HandoverUserID,Arrearage,Ispay,remark)");
            builder.Append(" values (");
            builder.Append("@UserID,@StartTime,@EedTime,@AddNewUser,@CardMoney,@ExpenseSumMoneys,@ExpenseBinkMoneys,@ExpenseCouponMoneys,@SRechargeMoney,@FRechargeMoney,@RechargeBank,@FRechargeGiveMoney,@AllMoneys,@sjMoneys,@HandoverUserID,@Arrearage,@Ispay,@remark)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@UserID", SqlDbType.Int, 4), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EedTime", SqlDbType.DateTime), new SqlParameter("@AddNewUser", SqlDbType.Int, 4), new SqlParameter("@CardMoney", SqlDbType.Money, 8), new SqlParameter("@ExpenseSumMoneys", SqlDbType.Money, 8), new SqlParameter("@ExpenseBinkMoneys", SqlDbType.Money, 8), new SqlParameter("@ExpenseCouponMoneys", SqlDbType.Money, 8), new SqlParameter("@SRechargeMoney", SqlDbType.Money, 8), new SqlParameter("@FRechargeMoney", SqlDbType.Money, 8), new SqlParameter("@RechargeBank", SqlDbType.Money, 8), new SqlParameter("@FRechargeGiveMoney", SqlDbType.Money, 8), new SqlParameter("@AllMoneys", SqlDbType.Money, 8), new SqlParameter("@sjMoneys", SqlDbType.Money, 8), new SqlParameter("@HandoverUserID", SqlDbType.Int, 4), new SqlParameter("@Arrearage", SqlDbType.Money, 8), 
                new SqlParameter("@Ispay", SqlDbType.Bit, 1), new SqlParameter("@remark", SqlDbType.NVarChar, 500)
             };
            cmdParms[0].Value = model.UserID;
            cmdParms[1].Value = model.StartTime;
            cmdParms[2].Value = model.EedTime;
            cmdParms[3].Value = model.AddNewUser;
            cmdParms[4].Value = model.CardMoney;
            cmdParms[5].Value = model.ExpenseSumMoneys;
            cmdParms[6].Value = model.ExpenseBinkMoneys;
            cmdParms[7].Value = model.ExpenseCouponMoneys;
            cmdParms[8].Value = model.SRechargeMoney;
            cmdParms[9].Value = model.FRechargeMoney;
            cmdParms[10].Value = model.RechargeBank;
            cmdParms[11].Value = model.FRechargeGiveMoney;
            cmdParms[12].Value = model.AllMoneys;
            cmdParms[13].Value = model.sjMoneys;
            cmdParms[14].Value = model.HandoverUserID;
            cmdParms[15].Value = model.Arrearage;
            cmdParms[0x10].Value = model.Ispay;
            cmdParms[0x11].Value = model.remark;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int SysUserWorkID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from SysUserWork ");
            builder.Append(" where SysUserWorkID=@SysUserWorkID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@SysUserWorkID", SqlDbType.Int, 4) };
            cmdParms[0].Value = SysUserWorkID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string SysUserWorkIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from SysUserWork ");
            builder.Append(" where SysUserWorkID in (" + SysUserWorkIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int SysUserWorkID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from SysUserWork");
            builder.Append(" where SysUserWorkID=@SysUserWorkID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@SysUserWorkID", SqlDbType.Int, 4) };
            cmdParms[0].Value = SysUserWorkID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select SysUserWorkID,UserID,StartTime,EedTime,AddNewUser,CardMoney,ExpenseSumMoneys,ExpenseBinkMoneys,ExpenseCouponMoneys,SRechargeMoney,FRechargeMoney,RechargeBank,FRechargeGiveMoney,AllMoneys,sjMoneys,HandoverUserID,Arrearage,Ispay,remark ");
            builder.Append(" FROM SysUserWork ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" SysUserWorkID,UserID,StartTime,EedTime,AddNewUser,CardMoney,ExpenseSumMoneys,ExpenseBinkMoneys,ExpenseCouponMoneys,SRechargeMoney,FRechargeMoney,RechargeBank,FRechargeGiveMoney,AllMoneys,sjMoneys,HandoverUserID,Arrearage,Ispay,remark ");
            builder.Append(" FROM SysUserWork ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.SysUserWorkID desc");
            }
            builder.Append(")AS Row, T.*  from SysUserWork T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListSP(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = "SysUserWork,SysUser,SysShop";
            string[] column = new string[] { "SysUser.UserName,SysUser.UserNumber,SysUser.UserShopID,SysShop.ShopName,SysUserWork.*" };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "EedTime", "SysUserWorkID", false, PageSize, PageIndex, recordCount);
            resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            return set;
        }

        public Chain.Model.SysUserWork GetModel(int SysUserWorkID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 SysUserWorkID,UserID,StartTime,EedTime,AddNewUser,CardMoney,ExpenseSumMoneys,ExpenseBinkMoneys,ExpenseCouponMoneys,SRechargeMoney,FRechargeMoney,RechargeBank,FRechargeGiveMoney,AllMoneys,sjMoneys,HandoverUserID,Arrearage,Ispay,remark from SysUserWork ");
            builder.Append(" where SysUserWorkID=@SysUserWorkID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@SysUserWorkID", SqlDbType.Int, 4) };
            cmdParms[0].Value = SysUserWorkID;
            Chain.Model.SysUserWork work = new Chain.Model.SysUserWork();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["SysUserWorkID"] != null) && (set.Tables[0].Rows[0]["SysUserWorkID"].ToString() != ""))
            {
                work.SysUserWorkID = int.Parse(set.Tables[0].Rows[0]["SysUserWorkID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["UserID"] != null) && (set.Tables[0].Rows[0]["UserID"].ToString() != ""))
            {
                work.UserID = int.Parse(set.Tables[0].Rows[0]["UserID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["StartTime"] != null) && (set.Tables[0].Rows[0]["StartTime"].ToString() != ""))
            {
                work.StartTime = DateTime.Parse(set.Tables[0].Rows[0]["StartTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["EedTime"] != null) && (set.Tables[0].Rows[0]["EedTime"].ToString() != ""))
            {
                work.EedTime = DateTime.Parse(set.Tables[0].Rows[0]["EedTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["AddNewUser"] != null) && (set.Tables[0].Rows[0]["AddNewUser"].ToString() != ""))
            {
                work.AddNewUser = int.Parse(set.Tables[0].Rows[0]["AddNewUser"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CardMoney"] != null) && (set.Tables[0].Rows[0]["CardMoney"].ToString() != ""))
            {
                work.CardMoney = decimal.Parse(set.Tables[0].Rows[0]["CardMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ExpenseSumMoneys"] != null) && (set.Tables[0].Rows[0]["ExpenseSumMoneys"].ToString() != ""))
            {
                work.ExpenseSumMoneys = decimal.Parse(set.Tables[0].Rows[0]["ExpenseSumMoneys"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ExpenseBinkMoneys"] != null) && (set.Tables[0].Rows[0]["ExpenseBinkMoneys"].ToString() != ""))
            {
                work.ExpenseBinkMoneys = decimal.Parse(set.Tables[0].Rows[0]["ExpenseBinkMoneys"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ExpenseCouponMoneys"] != null) && (set.Tables[0].Rows[0]["ExpenseCouponMoneys"].ToString() != ""))
            {
                work.ExpenseCouponMoneys = decimal.Parse(set.Tables[0].Rows[0]["ExpenseCouponMoneys"].ToString());
            }
            if ((set.Tables[0].Rows[0]["SRechargeMoney"] != null) && (set.Tables[0].Rows[0]["SRechargeMoney"].ToString() != ""))
            {
                work.SRechargeMoney = decimal.Parse(set.Tables[0].Rows[0]["SRechargeMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["FRechargeMoney"] != null) && (set.Tables[0].Rows[0]["FRechargeMoney"].ToString() != ""))
            {
                work.FRechargeMoney = decimal.Parse(set.Tables[0].Rows[0]["FRechargeMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["RechargeBank"] != null) && (set.Tables[0].Rows[0]["RechargeBank"].ToString() != ""))
            {
                work.RechargeBank = decimal.Parse(set.Tables[0].Rows[0]["RechargeBank"].ToString());
            }
            if ((set.Tables[0].Rows[0]["FRechargeGiveMoney"] != null) && (set.Tables[0].Rows[0]["FRechargeGiveMoney"].ToString() != ""))
            {
                work.FRechargeGiveMoney = decimal.Parse(set.Tables[0].Rows[0]["FRechargeGiveMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["AllMoneys"] != null) && (set.Tables[0].Rows[0]["AllMoneys"].ToString() != ""))
            {
                work.AllMoneys = decimal.Parse(set.Tables[0].Rows[0]["AllMoneys"].ToString());
            }
            if ((set.Tables[0].Rows[0]["sjMoneys"] != null) && (set.Tables[0].Rows[0]["sjMoneys"].ToString() != ""))
            {
                work.sjMoneys = decimal.Parse(set.Tables[0].Rows[0]["sjMoneys"].ToString());
            }
            if ((set.Tables[0].Rows[0]["HandoverUserID"] != null) && (set.Tables[0].Rows[0]["HandoverUserID"].ToString() != ""))
            {
                work.HandoverUserID = int.Parse(set.Tables[0].Rows[0]["HandoverUserID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["Arrearage"] != null) && (set.Tables[0].Rows[0]["Arrearage"].ToString() != ""))
            {
                work.Arrearage = decimal.Parse(set.Tables[0].Rows[0]["Arrearage"].ToString());
            }
            if ((set.Tables[0].Rows[0]["Ispay"] != null) && (set.Tables[0].Rows[0]["Ispay"].ToString() != ""))
            {
                if ((set.Tables[0].Rows[0]["Ispay"].ToString() == "1") || (set.Tables[0].Rows[0]["Ispay"].ToString().ToLower() == "true"))
                {
                    work.Ispay = true;
                }
                else
                {
                    work.Ispay = false;
                }
            }
            if ((set.Tables[0].Rows[0]["remark"] != null) && (set.Tables[0].Rows[0]["remark"].ToString() != ""))
            {
                work.remark = set.Tables[0].Rows[0]["remark"].ToString();
            }
            return work;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM SysUserWork ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Update(Chain.Model.SysUserWork model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update SysUserWork set ");
            builder.Append("UserID=@UserID,");
            builder.Append("StartTime=@StartTime,");
            builder.Append("EedTime=@EedTime,");
            builder.Append("AddNewUser=@AddNewUser,");
            builder.Append("CardMoney=@CardMoney,");
            builder.Append("ExpenseSumMoneys=@ExpenseSumMoneys,");
            builder.Append("ExpenseBinkMoneys=@ExpenseBinkMoneys,");
            builder.Append("ExpenseCouponMoneys=@ExpenseCouponMoneys,");
            builder.Append("SRechargeMoney=@SRechargeMoney,");
            builder.Append("FRechargeMoney=@FRechargeMoney,");
            builder.Append("RechargeBank=@RechargeBank,");
            builder.Append("FRechargeGiveMoney=@FRechargeGiveMoney,");
            builder.Append("AllMoneys=@AllMoneys,");
            builder.Append("sjMoneys=@sjMoneys,");
            builder.Append("HandoverUserID=@HandoverUserID,");
            builder.Append("Arrearage=@Arrearage,");
            builder.Append("Ispay=@Ispay,");
            builder.Append("remark=@remark");
            builder.Append(" where SysUserWorkID=@SysUserWorkID");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@UserID", SqlDbType.Int, 4), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EedTime", SqlDbType.DateTime), new SqlParameter("@AddNewUser", SqlDbType.Int, 4), new SqlParameter("@CardMoney", SqlDbType.Money, 8), new SqlParameter("@ExpenseSumMoneys", SqlDbType.Money, 8), new SqlParameter("@ExpenseBinkMoneys", SqlDbType.Money, 8), new SqlParameter("@ExpenseCouponMoneys", SqlDbType.Money, 8), new SqlParameter("@SRechargeMoney", SqlDbType.Money, 8), new SqlParameter("@FRechargeMoney", SqlDbType.Money, 8), new SqlParameter("@RechargeBank", SqlDbType.Money, 8), new SqlParameter("@FRechargeGiveMoney", SqlDbType.Money, 8), new SqlParameter("@AllMoneys", SqlDbType.Money, 8), new SqlParameter("@sjMoneys", SqlDbType.Money, 8), new SqlParameter("@HandoverUserID", SqlDbType.Int, 4), new SqlParameter("@Arrearage", SqlDbType.Money, 8), 
                new SqlParameter("@Ispay", SqlDbType.Bit, 1), new SqlParameter("@remark", SqlDbType.NVarChar, 500), new SqlParameter("@SysUserWorkID", SqlDbType.Int, 4)
             };
            cmdParms[0].Value = model.UserID;
            cmdParms[1].Value = model.StartTime;
            cmdParms[2].Value = model.EedTime;
            cmdParms[3].Value = model.AddNewUser;
            cmdParms[4].Value = model.CardMoney;
            cmdParms[5].Value = model.ExpenseSumMoneys;
            cmdParms[6].Value = model.ExpenseBinkMoneys;
            cmdParms[7].Value = model.ExpenseCouponMoneys;
            cmdParms[8].Value = model.SRechargeMoney;
            cmdParms[9].Value = model.FRechargeMoney;
            cmdParms[10].Value = model.RechargeBank;
            cmdParms[11].Value = model.FRechargeGiveMoney;
            cmdParms[12].Value = model.AllMoneys;
            cmdParms[13].Value = model.sjMoneys;
            cmdParms[14].Value = model.HandoverUserID;
            cmdParms[15].Value = model.Arrearage;
            cmdParms[0x10].Value = model.Ispay;
            cmdParms[0x11].Value = model.remark;
            cmdParms[0x12].Value = model.SysUserWorkID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

